<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-${liquibase.xsd-version}.xsd">

    <!-- Copyright 2019-2025 Steinar Bang                                                -->
    <!--                                                                                 -->
    <!-- Licensed under the Apache License, Version 2.0 (the "License");                 -->
    <!-- you may not use this file except in compliance with the License.                -->
    <!-- You may obtain a copy of the License at                                         -->
    <!--   http://www.apache.org/licenses/LICENSE-2.0                                    -->
    <!-- Unless required by applicable law or agreed to in writing,                      -->
    <!-- software distributed under the License is distributed on an "AS IS" BASIS,      -->
    <!-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.        -->
    <!-- See the License for the specific language governing permissions and limitations -->
    <!-- under the License.                                                              -->

    <changeSet author="sb" id="drop-administrators-table">
        <dropTable tableName="administrators" />
    </changeSet>

    <changeSet author="sb" id="drop-ukelonn-users-table">
        <dropTable tableName="ukelonn_users" />
    </changeSet>

    <changeSet author="sb" id="sum-over-year-view-derby">
        <preConditions onFail="CONTINUE" >
            <and>
                <not>
                    <viewExists viewName="sum_over_year_view" />
                </not>
                <dbms type="derby" />
            </and>
        </preConditions>

        <createView viewName="sum_over_year_view" fullDefinition="false" >select sum(t.transaction_amount) as aggregate_amount, YEAR(t.transaction_time) as aggregate_year, a.username as username from transactions t join transaction_types tt on tt.transaction_type_id=t.transaction_type_id join accounts a on a.account_id=t.account_id where tt.transaction_is_work group by YEAR(t.transaction_time), a.username order by YEAR(t.transaction_time)</createView>
    </changeSet>

    <changeSet author="sb" id="sum-over-year-view-postgresql">
        <preConditions onFail="CONTINUE" >
            <and>
                <not>
                    <viewExists viewName="sum_over_year_view" />
                </not>
                <dbms type="postgresql" />
            </and>
        </preConditions>

        <createView viewName="sum_over_year_view" fullDefinition="false">select sum(t.transaction_amount) as aggregate_amount, extract(year from t.transaction_time) as aggregate_year, a.username as username from transactions t join transaction_types tt on tt.transaction_type_id=t.transaction_type_id join accounts a on a.account_id=t.account_id where tt.transaction_is_work group by extract(year from t.transaction_time), a.username order by extract(year from t.transaction_time)</createView>
    </changeSet>

    <changeSet author="sb" id="sum-over-year-and-month-view-derby">
        <preConditions onFail="CONTINUE" >
            <and>
                <not>
                    <viewExists viewName="sum_over_year_and_month_view" />
                </not>
                <dbms type="derby" />
            </and>
        </preConditions>

        <createView viewName="sum_over_year_and_month_view" fullDefinition="false" >select sum(t.transaction_amount) as aggregate_amount, YEAR(t.transaction_time) as aggregate_year, MONTH(t.transaction_time) as aggregate_month, a.username as username from transactions t join transaction_types tt on tt.transaction_type_id=t.transaction_type_id join accounts a on a.account_id=t.account_id where tt.transaction_is_work group by YEAR(t.transaction_time), MONTH(t.transaction_time), a.username order by YEAR(t.transaction_time), MONTH(t.transaction_time)</createView>
    </changeSet>

    <changeSet author="sb" id="sum-over-year-and-month-view-postgresql">
        <preConditions onFail="CONTINUE" >
            <and>
                <not>
                    <viewExists viewName="sum_over_year_and_month_view" />
                </not>
                <dbms type="postgresql" />
            </and>
        </preConditions>

        <createView viewName="sum_over_year_and_month_view" fullDefinition="false">select sum(t.transaction_amount) as aggregate_amount, extract(year from t.transaction_time) as aggregate_year, extract(month from t.transaction_time) as aggregate_month, a.username as username from transactions t join transaction_types tt on tt.transaction_type_id=t.transaction_type_id join accounts a on a.account_id=t.account_id where tt.transaction_is_work group by extract(year from t.transaction_time), extract(month from t.transaction_time), a.username order by extract(year from t.transaction_time), extract(month from t.transaction_time)</createView>
    </changeSet>

    <changeSet author="sb" id="add-table-bonuses">
        <createTable tableName="bonuses">
            <column autoIncrement="true" name="bonus_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="bonus_id_key" />
            </column>
            <column name="enabled" type="BOOLEAN">
                <constraints nullable="false"/>
            </column>
            <column name="iconurl" type="VARCHAR(256)" />
            <column name="title" type="VARCHAR(64)">
                <constraints nullable="false"/>
            </column>
            <column name="description" type="VARCHAR(128)">
                <constraints nullable="false"/>
            </column>
            <column name="bonus_factor" type="DOUBLE">
                <constraints nullable="false"/>
            </column>
            <column name="start_date" type="TIMESTAMP">
                <constraints nullable="false"/>
            </column>
            <column name="end_date" type="TIMESTAMP">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet author="sb" id="bonuses-add-indexes">
        <createIndex indexName="idx_bonuses_enabled" tableName="bonuses">
            <column name="enabled" />
        </createIndex>
        <createIndex indexName="idx_bonuses_start_date" tableName="bonuses">
            <column name="start_date" />
        </createIndex>
        <createIndex indexName="idx_bonuses_end_date" tableName="bonuses">
            <column name="end_date" />
        </createIndex>
    </changeSet>

</databaseChangeLog>
